USE [STATION]
GO
/****** Object:  StoredProcedure [dbo].[sp_Nurse_GetPatient_LabResult_NormalValues_Tabular]    Script Date: 8/6/2018 5:15:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[sp_Nurse_GetPatient_LabResult_NormalValues_Tabular]
--declare
	@Hospnum		as Varchar(10)
AS

--set @Hospnum = '154358'


select (convert(datetime, (convert(varchar(10), a.transdate, 101))) + ' ' + convert(varchar(5), a.transdate, 114)), a.Hospnum, a.Idnum, a.Requestnum,
	case 
		when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P') then c.ItemID
		else a.labexamID
	end as labexamid,
	a.labsectionid ,
	case a.labexamid
		when '1' then d.labsection + ' (CBC)'
		else d.labsection
	end  as section ,
	case 
		when a.labexamid = '1' then ltrim(convert(varchar(30),e.shortname) + ' ' + convert(varchar(35),e.resultname))
		when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P' and labexamID <> '1') then g.labexam
		else b.labexam
		end as Exam ,
	case a.formtype
		when '1' then convert(varchar(10),cast(c.minvalue as decimal(10,2))) + ' - ' +	convert(varchar(10),cast(c.maxvalue as decimal(10,2))) + ' ' + c.unit 
		when '7' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when '0' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when 'U' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when 'P' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		else 'No Normal Value'
		END
		as NormalValue,
	case a.formtype
		when '1' then convert(varchar(10),cast(c.result as decimal(10,2)))
		when '7' then isnull(c.Strresult,'')
		when '0' then isnull(c.Strresult,'')
		when 'U' then isnull(c.Strresult,c.result)
		when 'P' then isnull(c.Strresult,c.result)
		else 'No Result' 
		END
		 as Result ,
	--convert(varchar(10),Isnull(a.Verifydate,a.ResultDate),101) + substring(convert(varchar(19),Isnull(a.Verifydate,a.ResultDate),100),12,8) as ResultDate,
	Isnull(a.Verifydate,a.ResultDate) as ResultDate,
	a.formtype as FormType, a.Transdate, c.SortOrder
from laboratory..tblablogbook a
left outer join build_file..tbcolabsection d
	on a.labsectionid = d.labsectionid
left outer join build_file..tbcolabexam b
	on a.labexamid = b.labexamid
left outer join laboratory..tblabresultnvalues c
	on a.requestnum = c.requestnum --and a.labexamid = c.itemid
left outer join build_file..tbcolabvalues e 
	on e.code = c.itemid
left outer join build_file..tbcolabexam g
	on c.Itemid = g.labexamid
where a.Hospnum = @Hospnum and a.labsectionid = 'A'
and (isnumeric(c.Strresult) = 1 )	
and (convert(datetime, (convert(varchar(10), a.transdate, 101))) + ' ' + convert(varchar(5), a.transdate, 114)) in 
(select distinct top 14 convert(datetime, (convert(varchar(10), transdate, 101))) + ' ' + convert(varchar(5), transdate, 114)
from laboratory..tbLabLogbook
where hospnum = @Hospnum and labsectionid = 'A'
order by convert(datetime, (convert(varchar(10), transdate, 101))) + ' ' + convert(varchar(5), transdate, 114) desc)


UNION ALL

select (convert(datetime, (convert(varchar(10), a.transdate, 101))) + ' ' + convert(varchar(5), a.transdate, 114)), a.Hospnum, a.Idnum, a.Requestnum,
	case 
		when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P') then c.ItemID
		else a.labexamID
	end as labexamid,
	a.labsectionid ,
	case a.labexamid
		when '1' then d.labsection + ' (CBC)'
		else d.labsection
	end  as section ,
	case 
		when a.labexamid = '1' then ltrim(convert(varchar(30),e.shortname) + ' ' + convert(varchar(35),e.resultname))
		when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P' and labexamID <> '1') then g.labexam
		else b.labexam
		end as Exam ,
	case a.formtype
		when '1' then convert(varchar(10),cast(c.minvalue as decimal(10,2))) + ' - ' +	convert(varchar(10),cast(c.maxvalue as decimal(10,2))) + ' ' + c.unit 
		when '7' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when '0' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when 'U' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when 'P' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		else 'No Normal Value'
		END
		as NormalValue,
	case a.formtype
		when '1' then convert(varchar(10),cast(c.result as decimal(10,2)))
		when '7' then isnull(c.Strresult,'')
		when '0' then isnull(c.Strresult,'')
		when 'U' then isnull(c.Strresult,c.result)
		when 'P' then isnull(c.Strresult,c.result)
		else 'No Result' 
		END
		 as Result ,
	--convert(varchar(10),Isnull(a.Verifydate,a.ResultDate),101) + substring(convert(varchar(19),Isnull(a.Verifydate,a.ResultDate),100),12,8) as ResultDate,
	Isnull(a.Verifydate,a.ResultDate) as ResultDate,
	a.formtype as FormType, a.Transdate, c.SortOrder
from laboratory..tblablogbook a
left outer join build_file..tbcolabsection d
	on a.labsectionid = d.labsectionid
left outer join build_file..tbcolabexam b
	on a.labexamid = b.labexamid
left outer join laboratory..tblabresultnvalues c
	on a.requestnum = c.requestnum --and a.labexamid = c.itemid
left outer join build_file..tbcolabvalues e 
	on e.code = c.itemid
left outer join build_file..tbcolabexam g
	on c.Itemid = g.labexamid
where a.Hospnum = @Hospnum and a.labsectionid = 'B'
and (isnumeric(c.Strresult) = 1 )	
and (convert(datetime, (convert(varchar(10), a.transdate, 101))) + ' ' + convert(varchar(5), a.transdate, 114)) in 
(
select distinct top 14 convert(datetime, (convert(varchar(10), transdate, 101))) + ' ' + convert(varchar(5), transdate, 114)
from laboratory..tbLabLogbook
where hospnum = @Hospnum and labsectionid = 'B'
order by convert(datetime, (convert(varchar(10), transdate, 101))) + ' ' + convert(varchar(5), transdate, 114) desc
)
order by section,SortOrder,NormalValue, Isnull(a.Verifydate,a.ResultDate); --added last 10/05/2106 By Dale for DocMod Lab Tabular